package com.sailfish.springbootdemo.dao.db1;

import com.sailfish.springbootdemo.common.DB1TableName;
import com.sailfish.springbootdemo.pojo.db1.UpkeepHistory;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;

public interface UpkeepHistoryDao {

    /**
     * 查询
     *
     * @param assetNumber
     * @return
     */
    @Results(id = "upkeepHistoryResultMap", value = {
            @Result(column = "upkeep_history_id", property = "upkeepHistoryId", jdbcType = JdbcType.INTEGER),
            @Result(column = "asset_number", property = "assetNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "create_time", property = "createTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "update_time", property = "updateTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "status", property = "status", jdbcType = JdbcType.INTEGER),
            @Result(column = "due_upkeep_time", property = "dueUpkeepTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "actual_upkeep_time", property = "actualUpkeepTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "owner_id", property = "ownerId", jdbcType = JdbcType.INTEGER),
            @Result(column = "upkeep_type", property = "upkeepType", jdbcType = JdbcType.INTEGER),
            @Result(column = "upkeep_notes", property = "upkeepNotes", jdbcType = JdbcType.VARCHAR),
            @Result(column = "is_current", property = "isCurrent", jdbcType = JdbcType.INTEGER),
            @Result(column = "audit_notes", property = "auditNotes", jdbcType = JdbcType.VARCHAR),
    })
    @Select("<script>" +
            "select * from " + DB1TableName.UPKEEP_HISTORY + " t1 left join " + DB1TableName.PCBA_DEVICE + " t2 on t1.asset_number = t2.asset_number left join " + DB1TableName.PCBA_LINE + " t3 on t2.pcba_line_id = t3.pcba_line_id where 1 = 1 " +
            "<if test=\"statusList != null and statusList.size() > 0 \">\n" +
            "                and t1.status in\n" +
            "                <foreach item=\"item\" index=\"index\" collection=\"statusList\"\n" +
            "                         open=\"(\" separator=\",\" close=\")\">\n" +
            "                    #{item}\n" +
            "                </foreach>\n" +
            "            </if>\n" +
            "<if test='assetNumber != null and assetNumber != \"\" '>" +
            " and t1.asset_number = #{assetNumber}" +
            "</if>" +
            "<if test='upkeepType != null and upkeepType != \"\" '>" +
            " and t1.upkeep_type = #{upkeepType}" +
            "</if>" +
            "<if test='pcbaCompanyId != null and pcbaCompanyId != \"\" '>" +
            " and t3.pcba_company_id = #{pcbaCompanyId}" +
            "</if>" +
            "<if test='isCurrent != null and isCurrent != \"\" '>" +
            " and t1.is_current = #{isCurrent}" +
            "</if>" +
            "<if test='searchValue != null and searchValue != \"\" '>" +
            " and (t1.asset_number like CONCAT('%',#{searchValue},'%'))" +
            "</if>" +
            " order by t1.status, t1.due_upkeep_time desc" +
            "</script>")
    List<UpkeepHistory> getUpkeepHistoryByAssetNumber(@Param("assetNumber") String assetNumber, @Param("statusList") List<Integer> statusList, @Param("upkeepType") Integer upkeepType, @Param("pcbaCompanyId") Integer pcbaCompanyId, @Param("isCurrent") Integer isCurrent, @Param("searchValue") String searchValue);

    /**
     * 根据id查询
     *
     * @param upkeepHistoryId
     * @return
     */
    @ResultMap(value = "upkeepHistoryResultMap")
    @Select("select * from " + DB1TableName.UPKEEP_HISTORY + " where upkeep_history_id = #{upkeepHistoryId}")
    UpkeepHistory getUpkeepHistoryById(@Param("upkeepHistoryId") Integer upkeepHistoryId);

    /**
     * 查询
     *
     * @param assetNumber
     * @param upkeepType
     * @return
     */
    @ResultMap(value = "upkeepHistoryResultMap")
    @Select("select * from " + DB1TableName.UPKEEP_HISTORY + " where asset_number = #{assetNumber} and upkeep_type = #{upkeepType} and DATE_FORMAT(due_upkeep_time,'%Y-%m') = #{dueUpkeepTime} and is_current = 1")
    List<UpkeepHistory> getUpkeepHistoryByAssetNumberAndDueUpkeepTime(@Param("assetNumber") String assetNumber, @Param("upkeepType") Integer upkeepType, @Param("dueUpkeepTime") String dueUpkeepTime);

    /**
     * 新增
     *
     * @param upkeepHistory
     * @return
     */
    @Insert("insert into " + DB1TableName.UPKEEP_HISTORY + "(asset_number,create_time,status,due_upkeep_time,upkeep_type,is_current,audit_notes) values " +
            "(#{upkeepHistory.assetNumber},#{upkeepHistory.createTime},#{upkeepHistory.status},#{upkeepHistory.dueUpkeepTime},#{upkeepHistory.upkeepType},#{upkeepHistory.isCurrent},#{upkeepHistory.auditNotes})")
    Integer addUpkeepHistory(@Param("upkeepHistory") UpkeepHistory upkeepHistory);

    /**
     * 更新
     *
     * @param upkeepHistory
     * @return
     */
    @Update("<script>" +
            "update " + DB1TableName.UPKEEP_HISTORY +
            "<set>" +
            "<if test='upkeepHistory.actualUpkeepTime != null '>" +
            " actual_upkeep_time = #{upkeepHistory.actualUpkeepTime}," +
            "</if>" +
            "<if test='upkeepHistory.status != null and upkeepHistory.status != \"\" '>" +
            " status = #{upkeepHistory.status}," +
            "</if>" +
            "<if test='upkeepHistory.ownerId != null and upkeepHistory.ownerId != \"\" '>" +
            " owner_id = #{upkeepHistory.ownerId}," +
            "</if>" +
            "<if test='upkeepHistory.upkeepNotes != null and upkeepHistory.upkeepNotes != \"\" '>" +
            " upkeep_notes = #{upkeepHistory.upkeepNotes}," +
            "</if>" +
            "<if test='upkeepHistory.isCurrent != null and upkeepHistory.isCurrent != \"\" '>" +
            " is_current = #{upkeepHistory.isCurrent}," +
            "</if>" +
            "<if test='upkeepHistory.auditNotes != null and upkeepHistory.auditNotes != \"\" '>" +
            " audit_notes = #{upkeepHistory.auditNotes}," +
            "</if>" +
            "<if test='upkeepHistory.updateTime != null '>" +
            " update_time = #{upkeepHistory.updateTime}," +
            "</if>" +
            "</set>" +
            " where asset_number = #{upkeepHistory.assetNumber} and upkeep_type = #{upkeepHistory.upkeepType}" +
            "</script>")
    Integer updateUpkeepHistoryByAssetNumberAndUpkeepType(@Param("upkeepHistory") UpkeepHistory upkeepHistory);

    @Update("<script>" +
            "update " + DB1TableName.UPKEEP_HISTORY +
            "<set>" +
            "<if test='upkeepHistory.actualUpkeepTime != null '>" +
            " actual_upkeep_time = #{upkeepHistory.actualUpkeepTime}," +
            "</if>" +
            "<if test='upkeepHistory.status != null and upkeepHistory.status != \"\" '>" +
            " status = #{upkeepHistory.status}," +
            "</if>" +
            "<if test='upkeepHistory.ownerId != null and upkeepHistory.ownerId != \"\" '>" +
            " owner_id = #{upkeepHistory.ownerId}," +
            "</if>" +
            "<if test='upkeepHistory.upkeepNotes != null and upkeepHistory.upkeepNotes != \"\" '>" +
            " upkeep_notes = #{upkeepHistory.upkeepNotes}," +
            "</if>" +
            "<if test='upkeepHistory.auditNotes != null and upkeepHistory.auditNotes != \"\" '>" +
            " audit_notes = #{upkeepHistory.auditNotes}," +
            "</if>" +
            "<if test='upkeepHistory.updateTime != null '>" +
            " update_time = #{upkeepHistory.updateTime}," +
            "</if>" +
            "</set>" +
            " where upkeep_history_id = #{upkeepHistory.upkeepHistoryId}" +
            "</script>")
    Integer updateUpkeepHistoryById(@Param("upkeepHistory") UpkeepHistory upkeepHistory);

    /**
     * 根据assetNumber 查询 逾期的待保养记录个数
     */
    @Select("select count(*) as total from " + DB1TableName.UPKEEP_HISTORY + " where asset_number = #{assetNumber} and status = 5 and is_current = 1")
    Integer getNumByAssetNumberOverdue(@Param("assetNumber") String assetNumber);

    /**
     * 查询某个资产下设备的 某个月份的某种保养计划的记录存不存在
     */
    @ResultMap(value = "upkeepHistoryResultMap")
    @Select("select * from " + DB1TableName.UPKEEP_HISTORY + " where is_current = 1 and asset_number = #{assetNumber} and upkeep_type = #{upkeepType} and DATE_FORMAT(due_upkeep_time,'%Y-%m') = #{dueUpkeepMonth}")
    List<UpkeepHistory> getUpkeepHistoryByAssetNumberAndMonthAndUpkeepType(@Param("assetNumber") String assetNumber, @Param("upkeepType") Integer upkeepType, @Param("dueUpkeepMonth") String dueUpkeepMonth);
}
